﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using Stone.Library.Common;

namespace Stone.Library.DB
{
    public class DBExecuteUtil:DBUtil
    {
        #region Execute Region
        /// <summary>
        /// 执行cud，无参数形式
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int executeSql(string sql)
        {
            return executeSql(sql, null);
        }

        /// <summary>
        /// 多参数形式
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int executeSql(string sql, Hashtable hts)
        {
            return executeCud(sql, CommandType.Text, hts, null);
        }

        /// <summary>
        /// 通过设置参数SqlParameter进行查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static int executeSqlWithPar(string sql, SqlParameter[] sqlParams)
        {
            return executeCud(sql, CommandType.Text, null, sqlParams);
        }

        #endregion

        #region Query region, return string[][]
        /// <summary>
        /// 执行查询语句,返回交错数组
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string[][] querySqlArray(string sql)
        {
            return convertTableToArray(querySqlTable(sql));
        }

        public static string[][] querySqlArray(string sql, Hashtable args)
        {
            return convertTableToArray(querySqlTable(sql, args));
        }

        public static string[][] querySqlArray(string sql, SqlParameter[] sqlParas)
        {
            return convertTableToArray(querySqlTable(sql, sqlParas));
        }
        #endregion

        #region Query region,return Datatable
        /// <summary>
        /// 执行查询语句,返回dataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable querySqlTable(string sql)
        {
            return queryTable(sql, CommandType.Text, null, null);
        }

        public static DataTable querySqlTable(string sql, Hashtable args)
        {
            DataTable dt = queryTable(sql, CommandType.Text, args, null);
            //return queryTable(sql, CommandType.Text, args,null);
            return dt;
        }

        public static DataTable querySqlTable(string sql, SqlParameter[] sqlParas)
        {
            return queryTable(sql, CommandType.Text, null, sqlParas);
        }

        #endregion

        #region Oher
        /// <summary>
        /// 获得唯一的记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string getUniqueResult(string sql)
        {
            return getUniqueResult(sql, null, null);
        }

        /// <summary>
        /// 获得唯一的记录
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static string getUniqueResult(string sql, Hashtable args)
        {
            return getUniqueResult(sql, args, null);
        }

        public static string getUniqueResult(string sql, SqlParameter[] paras)
        {
            return getUniqueResult(sql, null, paras);
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            return Exists(strSql, null);
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, SqlParameter[] paras)
        {
            string obj = getUniqueResult(strSql, null, paras);
            int cmdresult;
            if (Object.Equals(obj, string.Empty))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj);
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region Page region

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageObject"></param>
        public static void findDataByPage(string sql, PageObject pageObject)
        {
            findDataByPage(sql, new Hashtable(), pageObject);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="hst"></param>
        /// <param name="pageObject"></param>
        public static void findDataByPage(string sql, Hashtable hst, PageObject pageObject)
        {
            sql = sql.ToUpper();
            if (pageObject.AutoCount)
            {
                getTotalCount(sql, hst, null, pageObject);
            }

            if (!pageObject.isFirstSetted())
            {
                if (pageObject.IsResultTable)
                    pageObject.TableData = querySqlTable(sql, hst);
                else
                    pageObject.ArrayData = querySqlArray(sql, hst);
                return;
            }
            StringBuilder sb = new StringBuilder("select * from (");
            sb.Append(sqlAddRowNumber(sql, pageObject));
            sb.Append(") _myResult where RowNumber>")
                .Append(pageObject.getFirst()).Append(" and RowNumber<").Append(pageObject.getLast());
            if (pageObject.IsResultTable)
                pageObject.TableData = querySqlTable(sb.ToString(), hst);
            else
                pageObject.ArrayData = querySqlArray(sb.ToString(), hst);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParas"></param>
        /// <param name="pageObject"></param>
        public static void findDataByPage(string sql, SqlParameter[] sqlParas, PageObject pageObject)
        {
            sql = sql.ToUpper();
            if (pageObject.AutoCount)
            {
                getTotalCount(sql, null, sqlParas, pageObject);
            }

            if (!pageObject.isFirstSetted())
            {
                if (pageObject.IsResultTable)
                    pageObject.TableData = querySqlTable(sql, sqlParas);
                else
                    pageObject.ArrayData = querySqlArray(sql, sqlParas);
                return;
            }
            StringBuilder sb = new StringBuilder("select * from (");
            sb.Append(sqlAddRowNumber(sql, pageObject));
            sb.Append(") _myResult where RowNumber>")
                .Append(pageObject.getFirst()).Append(" and RowNumber<").Append(pageObject.getLast());
            if (pageObject.IsResultTable)
                pageObject.TableData = querySqlTable(sb.ToString(), sqlParas);
            else
                pageObject.ArrayData = querySqlArray(sb.ToString(), sqlParas);
        }

        /// <summary>
        /// 生成增加RowNumber的sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageObject"></param>
        /// <returns></returns>
        private static StringBuilder sqlAddRowNumber(string sql, PageObject pageObject)
        {
            int fromIndex = sql.IndexOf("FROM");
            if (fromIndex == 0)
            {
                return new StringBuilder();
            }
            if (pageObject.OrderField == null || pageObject.OrderField == string.Empty)
            {
                throw new ArgumentException("必须设置Page对象的OrderField(排序)属性");
            }
            StringBuilder sqlBuilder = new StringBuilder(sql.Substring(0, fromIndex));

            sqlBuilder.Append(",ROW_NUMBER() OVER (ORDER BY ").Append(pageObject.OrderField)
            .Append(") AS RowNumber ");

            sqlBuilder.Append(sql.Substring(fromIndex, sql.Length - fromIndex));

            return sqlBuilder;
        }

        /// <summary>
        /// 获得总数目
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="pageObject"></param>
        private static void getTotalCount(string sql, Hashtable hst,
            SqlParameter[] sqlParas, PageObject pageObject)
        {
            int fromIndex = sql.IndexOf("FROM");
            if (fromIndex == 0)
            {
                pageObject.TotalCount = 0;
                return;
            }
            StringBuilder sb = new StringBuilder();
            if (pageObject.GroupFlag)       //如果含有Group by语句
            {
                sb.Append("SELECT COUNT(").Append(pageObject.CountField).Append(") from (")
                .Append(sql).Append(") COUNT_TABLE");
            }
            else
            {
                sb.Append("SELECT COUNT(").Append(pageObject.CountField).Append(")")
                .Append(sql.Substring(fromIndex, sql.Length - fromIndex));
            }

            string result = getUniqueResult(sb.ToString(), hst, sqlParas).ToString();
            if (result != string.Empty) pageObject.TotalCount = Convert.ToInt64(result);
            else pageObject.TotalCount = 0;
        }
				#endregion
    }
}
